import sqlite3
import datetime
import time
from typing import List, Optional, Dict, Any, Tuple
from models.word import Word

class DBManager:
    def __init__(self, db_path: str = "vocabulary.db", max_retries: int = 3, retry_delay: float = 0.1):
        self.db_path = db_path
        self.connection = None  # 当前连接，初始为None
        self.max_retries = max_retries  # 最大重试次数
        self.retry_delay = retry_delay  # 重试延迟时间(秒)
        self._init_db()

    def _connect(self) -> sqlite3.Connection:
        """建立新的数据库连接，带重试机制，不复用旧连接"""
        retries = 0
        while retries < self.max_retries:
            try:
                # 每次都创建新连接，不复用旧连接
                conn = sqlite3.connect(
                    self.db_path,
                    timeout=5,  # 增加超时时间
                    isolation_level=None  # 设置为自动提交模式
                )
                conn.row_factory = sqlite3.Row
                self.connection = conn  # 保存新连接
                return conn
            except sqlite3.OperationalError as e:
                if "locked" in str(e) and retries < self.max_retries - 1:
                    retries += 1
                    time.sleep(self.retry_delay * (2 ** retries))  # 指数退避策略
                    continue
                raise e

    def _close(self) -> None:
        """安全关闭数据库连接"""
        if self.connection:
            try:
                self.connection.close()
            except Exception:
                pass
            finally:
                self.connection = None  # 确保连接关闭后设置为None

    def _execute_with_retry(self, func):
        """带重试机制的数据库操作执行器"""
        retries = 0
        while retries < self.max_retries:
            try:
                return func()
            except sqlite3.OperationalError as e:
                if "locked" in str(e) and retries < self.max_retries - 1:
                    retries += 1
                    time.sleep(self.retry_delay * (2 ** retries))
                    continue
                raise e
            finally:
                # 确保每次操作后关闭连接
                self._close()

    def _init_db(self) -> None:
        """初始化数据库，创建必要的表"""
        def init():
            conn = self._connect()
            cursor = conn.cursor()
            
            # 创建单词表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS words (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                word TEXT NOT NULL,
                phonetic TEXT,
                meaning TEXT NOT NULL,
                example TEXT,
                category TEXT,
                mastery_level INTEGER DEFAULT 0,
                review_count INTEGER DEFAULT 0,
                last_review TIMESTAMP,
                next_review TIMESTAMP,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(word)
            )
            ''')
            
            # 创建学习记录表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS learning_stats (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date DATE DEFAULT CURRENT_DATE,
                words_learned INTEGER DEFAULT 0,
                words_reviewed INTEGER DEFAULT 0,
                UNIQUE(date)
            )
            ''')
            
            conn.commit()
            
        self._execute_with_retry(init)

    def add_word(self, word: Word) -> Tuple[bool, str]:
        """添加单个单词"""
        def add():
            conn = self._connect()
            cursor = conn.cursor()
            
            cursor.execute('''
            INSERT OR IGNORE INTO words 
            (word, phonetic, meaning, example, category, mastery_level)
            VALUES (?, ?, ?, ?, ?, ?)
            ''', (word.word, word.phonetic, word.meaning, word.example, 
                  word.category, word.mastery_level))
            
            conn.commit()
            
            if cursor.rowcount > 0:
                return True, f"成功添加单词: {word.word}"
            else:
                return False, f"单词已存在: {word.word}"
                
        return self._execute_with_retry(add)

    def bulk_add_words(self, words: List[Word]) -> Tuple[int, str]:
        """批量添加单词"""
        if not words:
            return 0, "没有可添加的单词数据"
            
        def bulk_add():
            conn = self._connect()
            cursor = conn.cursor()
            
            data = []
            for word in words:
                data.append((word.word, word.phonetic, word.meaning, 
                            word.example, word.category, word.mastery_level))
            
            cursor.executemany('''
            INSERT OR IGNORE INTO words 
            (word, phonetic, meaning, example, category, mastery_level)
            VALUES (?, ?, ?, ?, ?, ?)
            ''', data)
            
            conn.commit()
            return cursor.rowcount, f"成功添加 {cursor.rowcount} 个单词（重复单词已跳过）"
            
        return self._execute_with_retry(bulk_add)

    def get_word(self, word_id: int) -> Optional[Word]:
        """根据ID获取单词"""
        def get():
            conn = self._connect()
            cursor = conn.cursor()
            
            cursor.execute('SELECT * FROM words WHERE id = ?', (word_id,))
            row = cursor.fetchone()
            
            if row:
                return Word(
                    word=row['word'],
                    phonetic=row['phonetic'],
                    meaning=row['meaning'],
                    example=row['example'],
                    category=row['category'],
                    mastery_level=row['mastery_level']
                )
            return None
            
        return self._execute_with_retry(get)

    def get_words_to_review(self, limit: int = 10) -> List[Dict[str, Any]]:
        """获取待复习的单词，优先推送记忆模糊和未学习的单词"""
        def get_review_words():
            conn = self._connect()
            cursor = conn.cursor()
            
            today = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            # 优化排序：优先记忆模糊(mastery_level=1)，然后未学习(mastery_level=0)，再按复习时间和次数排序
            cursor.execute('''
            SELECT * FROM words 
            WHERE next_review <= ? OR next_review IS NULL
            ORDER BY 
                CASE mastery_level 
                    WHEN 1 THEN 0  
                    WHEN 0 THEN 1  
                    ELSE 2         
                END,
                next_review ASC,
                review_count ASC
            LIMIT ?
            ''', (today, limit))
            
            rows = cursor.fetchall()
            return [dict(row) for row in rows]
            
        return self._execute_with_retry(get_review_words)

    def update_word_mastery(self, word_id: int, mastery_level: int) -> Tuple[bool, str]:
        """更新单词掌握程度，并根据SM2算法更新复习时间"""
        def update_mastery():
            conn = self._connect()
            cursor = conn.cursor()
            
            # 获取当前单词信息
            cursor.execute('SELECT * FROM words WHERE id = ?', (word_id,))
            row = cursor.fetchone()
            
            if not row:
                return False, "单词不存在"
                
            # 使用简化的SM2算法计算下次复习时间
            review_count = row['review_count'] + 1
            interval = self._calculate_interval(review_count, mastery_level)
            next_review = datetime.datetime.now() + datetime.timedelta(days=interval)
            
            cursor.execute('''
            UPDATE words 
            SET mastery_level = ?, 
                review_count = ?,
                last_review = CURRENT_TIMESTAMP,
                next_review = ?
            WHERE id = ?
            ''', (mastery_level, review_count, next_review.strftime('%Y-%m-%d %H:%M:%S'), word_id))
            
            # 更新学习统计
            self._update_learning_stats(reviewed=1, conn=conn)
            
            conn.commit()
            return True, f"单词状态已更新，下次复习时间: {next_review.strftime('%Y-%m-%d')}"
            
        return self._execute_with_retry(update_mastery)

    def _calculate_interval(self, review_count: int, mastery_level: int) -> int:
        """根据复习次数和掌握程度计算下次复习间隔（天）"""
        if mastery_level < 2:  # 未掌握/模糊
            return 1 if review_count <= 3 else min(review_count, 7)
        elif mastery_level == 2:  # 基本掌握
            return [1, 3, 7, 14, 30][min(review_count-1, 4)]
        else:  # 已掌握
            return [3, 7, 14, 30, 60, 90][min(review_count-1, 5)]

    def _update_learning_stats(self, learned: int = 0, reviewed: int = 0, conn: sqlite3.Connection = None) -> None:
        """更新学习统计"""
        def update_stats():
            local_conn = conn or self._connect()
            cursor = local_conn.cursor()
            
            # 检查今天是否已有记录
            cursor.execute('SELECT * FROM learning_stats WHERE date = CURRENT_DATE')
            row = cursor.fetchone()
            
            if row:
                # 更新现有记录
                cursor.execute('''
                UPDATE learning_stats 
                SET words_learned = words_learned + ?,
                    words_reviewed = words_reviewed + ?
                WHERE date = CURRENT_DATE
                ''', (learned, reviewed))
            else:
                # 创建新记录
                cursor.execute('''
                INSERT INTO learning_stats (words_learned, words_reviewed)
                VALUES (?, ?)
                ''', (learned, reviewed))
                
            if not conn:
                local_conn.commit()
                
        if conn:
            # 如果传入了连接，直接执行不重试（由调用者处理）
            update_stats()
        else:
            self._execute_with_retry(update_stats)

    def import_from_json(self, json_data: List[Dict[str, Any]]) -> Tuple[int, str]:
        """从JSON数据导入单词"""
        words = []
        for item in json_data:
            word = Word(
                word=item.get('word', ''),
                phonetic=item.get('phonetic', ''),
                meaning=item.get('meaning', ''),
                example=item.get('example', ''),
                category=item.get('category', '')
            )
            if word.word and word.meaning:  # 确保单词和释义不为空
                words.append(word)
                
        if not words:
            return 0, "未找到有效的单词数据"
            
        return self.bulk_add_words(words)
        
    def get_streak_days(self) -> int:
        """计算连续学习天数"""
        def calculate_streak():
            conn = self._connect()
            cursor = conn.cursor()
            
            # 获取所有学习日期
            cursor.execute('SELECT date FROM learning_stats ORDER BY date ASC')
            date_rows = cursor.fetchall()
            
            if not date_rows:
                return 0
                
            # 转换为日期对象列表
            dates = [datetime.datetime.strptime(row[0], '%Y-%m-%d').date() for row in date_rows]
            today = datetime.date.today()
            
            # 检查最后一个学习日期是否是今天或昨天
            last_date = dates[-1]
            if (today - last_date).days > 1:
                return 0
                
            # 计算连续天数
            streak = 1
            max_streak = 1
            
            for i in range(1, len(dates)):
                prev_date = dates[i-1]
                curr_date = dates[i]
                
                if (curr_date - prev_date).days == 1:
                    streak += 1
                    max_streak = max(max_streak, streak)
                elif (curr_date - prev_date).days > 1:
                    streak = 1
                    
            return max_streak
            
        return self._execute_with_retry(calculate_streak)